# 创建请求参数
import json
from time import sleep

import pandas as pd
import requests

from balance_request_params import GlBookBalanceRequestParams
from get_config import get_token

def get_std(beginDate, endDate):
    params = GlBookBalanceRequestParams(beginDate=beginDate).set_end_date(endDate)

    # 生成请求体
    request_body = params.to_request_body()

    TOKEN = get_token()

    url_start = "https://jcdz.jcszfw.com/v1/cw/asyncRequest/start"
    headers = {"Token": TOKEN, "Content-Type": "application/json"}

    response = requests.post(url_start, json=request_body, headers=headers)

    if response.status_code != 200:
        print(f"❌ 初始请求失败: {response.status_code} - {response.text}")
        exit(1)

    try:
        start_data = response.json()
        if not start_data.get("result"):
            print(f"❌ 业务失败: {start_data}")
            exit(1)
        sequence_no = start_data["value"]

        return sequence_no
        print(f"✅ 获取到 sequenceNo: {sequence_no}")


    except Exception as e:
        print(f"❌ 解析初始响应失败: {e}")
        exit(1)

def get_bill_detail(sequence_no):
    TOKEN = get_token()
    headers = {"Token": TOKEN, "Content-Type": "application/json"}
    # === 第二步：轮询查询结果 ===
    url_query = "https://jcdz.jcszfw.com/v1/cw/asyncRequest/queryResult"
    query_body = {"sequenceNo": sequence_no}

    resp = requests.post(url_query, json=query_body, headers=headers)

    try:
        # 取出数据
        data = resp.json()['value']['data']
        return data


    except Exception as e:
        sleep(5)
        resp = requests.post(url_query, json=query_body, headers=headers)
        data = resp.json()['value']['data']

        return data


'''''
data -> details[]
accountCode 科目
accountName 科目名字
periodBeginAmountDr 期初借方
periodBeginAmountCr 期初贷方
origAmountDr 期间借方
origAmountCr期间贷方
periodEndOrigAmountDr期末余额借方
periodEndOrigAmountCr期末余额贷方
'''


def json_to_excel(data_list, output_file='余额表.xlsx'):
    """
    将JSON数据列表转换为Excel文件

    Args:
        data_list: 包含多个JSON对象的列表
        output_file: 输出的Excel文件名
    """

    # 定义需要提取的字段及其对应的中文列名
    columns_mapping = {
        'accountCode': '科目',
        'accountName': '科目名字',
        'periodBeginAmountDr': '期初借方',
        'periodBeginAmountCr': '期初贷方',
        'origAmountDr': '期间借方',
        'origAmountCr': '期间贷方',
        'periodEndOrigAmountDr': '期末余额借方',
        'periodEndOrigAmountCr': '期末余额贷方'
    }

    # 提取需要的数据
    extracted_data = []

    for item in data_list:
        row_data = {}
        for eng_key, cn_key in columns_mapping.items():
            # 使用get方法避免键不存在时报错，默认值为None
            row_data[cn_key] = item.get(eng_key, None)
        extracted_data.append(row_data)

    # 创建DataFrame
    df = pd.DataFrame(extracted_data)

    # 确保列的顺序与定义的顺序一致
    column_order = list(columns_mapping.values())
    df = df[column_order]

    # 写入Excel文件
    with pd.ExcelWriter(output_file, engine='openpyxl') as writer:
        df.to_excel(writer, sheet_name='Sheet1', index=False)

        # 获取工作表对象，调整列宽（可选）
        worksheet = writer.sheets['Sheet1']
        for column in worksheet.columns:
            max_length = 0
            column_letter = column[0].column_letter

            for cell in column:
                try:
                    if len(str(cell.value)) > max_length:
                        max_length = len(str(cell.value))
                except:
                    pass

            adjusted_width = min(max_length + 2, 50)
            worksheet.column_dimensions[column_letter].width = adjusted_width

    print(f"数据已成功写入到 {output_file}")
    return df


def main():
    sequence_no = get_std("2021-01", "2025-09")
    data = get_bill_detail(sequence_no)
    print(data)
    data_list=data['details']
    json_to_excel(data_list)


if __name__ == "__main__":
    main()